﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ChoWorkflowServer.Model;
using Cinchoo.Core.Xml.Serialization;
using Cinchoo.Core;
using Cinchoo.Core.Configuration;
using Cinchoo.Core.Diagnostics;
using System.Data.SQLite;
using System.Data;
using System.Threading;
using System.Data.Common;
using Cinchoo.Core.ETL;
using System.Runtime.Remoting.Contexts;
using Cinchoo.Core.Reflection;

namespace ChoWorkflowServer.ModelSqlite
{
    [Synchronization]
    public class ChoDataModel : ChoSynchronizedDataModelImpl
    {
        private static readonly ChoModelSettings _modelSettings = new ChoModelSettings();

        public ChoDataModel()
        {
        }

        protected override IDataReader GetAllTaskGroups(IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_TASKS_GRPS, conn, trans, CommandType.Text);
            return cmd.ExecuteReader();
        }

        protected override int GetTaskId(string taskName, IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_TASK_ID, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@TASK_NAME", taskName.ToDbValue<string>()));
                return cmd.ExecuteScalar().CastTo<int>();
            }
        }

        protected override bool IsTaskExists(string taskName, IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.IS_TASK_EXISTS, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@TASK_NAME", taskName));

                object ret = cmd.ExecuteScalar();
                if (ret == null)
                    return false;

                return ret.CastTo<bool>();
            }
        }

        protected override int AddTask(int taskId, string taskName, string workflowName, string taskDesc, int taskGroupId, 
            string cmdParameters, int? maxNoOfRetry, int? sleepBetweenRetry, int? timeout, string hostName, string modifiedBy, 
            string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = null;
            cmd = CreateCommand(ChoDataModelSql.INSERT_TASK, conn, trans, CommandType.Text);
            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@TASK_ID", taskId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@TASK_NAME", taskName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@WORKFLOW_NAME", workflowName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@TASK_DESC", taskDesc.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@TASK_GRP_ID", taskGroupId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@CMD_PARAMETERS", cmdParameters.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@NO_OF_RETRY", maxNoOfRetry.ToDbValue()));
                cmd.Parameters.Add(CreateParameter("@SLEEP_BETWEEN_RETRY", sleepBetweenRetry.ToDbValue()));
                cmd.Parameters.Add(CreateParameter("@TIMEOUT", timeout.ToDbValue()));
                cmd.Parameters.Add(CreateParameter("@MACHINE_NAME", hostName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_BY", modifiedBy.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", DateTime.Now));

                cmd.ExecuteNonQuery();
            }
            return taskId;
        }

        protected override int UpdateTask(int taskId, string taskName, string workflowName, string taskDesc, int taskGroupId, 
            string cmdParameters, int? maxNoOfRetry, int? sleepBetweenRetry, int? timeout, string hostName, string modifiedBy,
            string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = null;
            cmd = CreateCommand(ChoDataModelSql.UPDATE_TASK, conn, trans, CommandType.Text);
            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@TASK_ID", taskId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@TASK_NAME", taskName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@WORKFLOW_NAME", workflowName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@TASK_DESC", taskDesc.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@TASK_GRP_ID", taskGroupId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@CMD_PARAMETERS", cmdParameters.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@NO_OF_RETRY", maxNoOfRetry.ToDbValue()));
                cmd.Parameters.Add(CreateParameter("@SLEEP_BETWEEN_RETRY", sleepBetweenRetry.ToDbValue()));
                cmd.Parameters.Add(CreateParameter("@TIMEOUT", timeout.ToDbValue()));
                cmd.Parameters.Add(CreateParameter("@MACHINE_NAME", hostName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_BY", modifiedBy.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", DateTime.Now));

                cmd.ExecuteNonQuery();
            }
            return taskId;
        }

        protected override void MoveTask(string taskName, int destTaskGrpId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.MOVE_TASK, conn, trans, CommandType.Text);
            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@TASK_NAME", taskName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@TASK_GRP_ID", destTaskGrpId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_BY", modifiedBy));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", DateTime.Now));

                cmd.ExecuteNonQuery();
            }
        }
        protected override bool IsTaskExistsInGroup(string taskName, int grpId, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.IS_TASK_EXISTS_IN_GROUP, conn, trans, CommandType.Text);
            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@TASK_NAME", taskName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@TASK_GRP_ID", grpId.ToDbValue<int>()));
                return cmd.ExecuteScalar().CastTo<int>() == 1;
            }
        }

        protected override void DeleteTask(string taskName, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.DELETE_TASK, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@TASK_NAME", taskName));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_BY", modifiedBy));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", DateTime.Now));

                cmd.ExecuteNonQuery();
            }
        }

        protected override IDataReader GetAllUITasks(IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_TASKS_UI1, conn, trans, CommandType.Text);
            return cmd.ExecuteReader();
        }

        protected override IDataReader GetUITask(string taskName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_TASKS_UI2, conn, trans, CommandType.Text);
            cmd.Parameters.Add(CreateParameter("@TASK_NAME", taskName.ToDbValue<string>()));
            return cmd.ExecuteReader();
        }

        protected override IDataReader GetUITasksForGroup(string taskGrpName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_TASKS_UI3, conn, trans, CommandType.Text);
            cmd.Parameters.Add(CreateParameter("@TASK_GRP_NAME", taskGrpName.ToDbValue<string>()));
            return cmd.ExecuteReader();
        }

        protected override IDataReader GetUITasks(string taskName, string taskGrpName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_TASKS_UI4, conn, trans, CommandType.Text);
            cmd.Parameters.Add(CreateParameter("@TASK_NAME", taskName.ToDbValue<string>()));
            cmd.Parameters.Add(CreateParameter("@TASK_GRP_NAME", taskGrpName.ToDbValue<string>()));
            return cmd.ExecuteReader();
        }

        protected override IDataReader GetAllTasks(string hostName, DateTime modifiedDate, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_TASKS1, conn, trans, CommandType.Text);
            cmd.Parameters.Add(CreateParameter("@MACHINE_NAME", hostName.ToDbValue<string>()));
            cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", modifiedDate));
            return cmd.ExecuteReader();
        }

        protected override IDataReader GetTask(string taskName, string hostName, DateTime modifiedDate, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_TASKS2, conn, trans, CommandType.Text);
            cmd.Parameters.Add(CreateParameter("@TASK_NAME", taskName.ToDbValue<string>()));
            cmd.Parameters.Add(CreateParameter("@MACHINE_NAME", hostName.ToDbValue<string>()));
            cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", modifiedDate));
            return cmd.ExecuteReader();
        }

        protected override IDataReader GetTasksForGroup(string taskGrpName, string hostName, DateTime modifiedDate, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_TASKS3, conn, trans, CommandType.Text);
            cmd.Parameters.Add(CreateParameter("@TASK_GRP_NAME", taskGrpName.ToDbValue<string>()));
            cmd.Parameters.Add(CreateParameter("@MACHINE_NAME", hostName.ToDbValue<string>()));
            cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", modifiedDate));
            return cmd.ExecuteReader();
        }

        protected override IDataReader GetTasks(string taskName, string taskGrpName, string hostName, DateTime modifiedDate, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_TASKS4, conn, trans, CommandType.Text);
            cmd.Parameters.Add(CreateParameter("@TASK_NAME", taskName.ToDbValue<string>()));
            cmd.Parameters.Add(CreateParameter("@TASK_GRP_NAME", taskGrpName.ToDbValue<string>()));
            cmd.Parameters.Add(CreateParameter("@MACHINE_NAME", hostName.ToDbValue<string>()));
            cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", modifiedDate));
            return cmd.ExecuteReader();
        }

        protected override int GetTaskGroupId(string taskGroupName, IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_TASK_GRP_ID, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@TASK_GRP_NAME", taskGroupName.ToDbValue<string>()));
                return cmd.ExecuteScalar().CastTo<int>();
            }
        }

        protected override int AddTaskGroup(int taskGroupId, string taskGroupName, string taskGroupDesc, int parent, string modifiedBy, 
            string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.INSERT_TASK_GROUP, conn, trans, CommandType.Text);
            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@TASK_GRP_ID", taskGroupId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@TASK_GRP_NAME", taskGroupName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@TASK_GRP_DESC", taskGroupDesc.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@PARENT", parent));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_BY", modifiedBy.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", DateTime.Now));

                cmd.ExecuteNonQuery();
            }

            return taskGroupId;
        }

        protected override int UpdateTaskGroup(int taskGroupId, string taskGroupName, string taskGroupDesc, int parent, string modifiedBy, 
            string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.UPDATE_TASK_GROUP, conn, trans, CommandType.Text);
            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@TASK_GRP_ID", taskGroupId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@TASK_GRP_NAME", taskGroupName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@TASK_GRP_DESC", taskGroupDesc.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@PARENT", parent));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_BY", modifiedBy.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", DateTime.Now));

                cmd.ExecuteNonQuery();
            }

            return taskGroupId;
        }

        protected override void DeleteTaskGroup(string taskGroupName, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.DELETE_TASK_GROUP, conn, null, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@TASK_GRP_NAME", taskGroupName));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_BY", modifiedBy));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", DateTime.Now));

                cmd.ExecuteNonQuery();
            }
        }

        protected override bool IsTaskGroupExists(string taskGrpName, IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.IS_TASK_GROUP_EXISTS, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@TASK_GRP_NAME", taskGrpName));

                object ret = cmd.ExecuteScalar();
                if (ret == null)
                    return false;

                return ret.CastTo<bool>();
            }
        }

        protected override IDataReader GetTaskScheduleTypes(IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_SCHEDULE_TYPES, conn, trans, CommandType.Text);
            return cmd.ExecuteReader();
        }

        protected override IDataReader GetTaskSchedules(int taskId, string scheduleName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_TASK_SCHEDULES, conn, null, CommandType.Text);
            cmd.Parameters.Add(CreateParameter("@TASK_ID", taskId.ToDbValue<int>()));
            cmd.Parameters.Add(CreateParameter("@SCHEDULE_NAME", scheduleName.ToDbValue<string>()));

            return cmd.ExecuteReader();
        }

        protected override int GetTaskScheduleId(string scheduleName, int taskId, IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_TASK_SCHEDULE_ID, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@SCHEDULE_NAME", scheduleName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@TASK_ID", taskId.ToDbValue<int>()));
                return cmd.ExecuteScalar().CastTo<int>();
            }
        }

        protected override int AddTaskSchedule(int taskScheduleId, ChoTasksDataSet.CHO_TASK_SCHEDULESRow row, string modifiedBy, 
            string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.INSERT_TASK_SCHEDULE, conn, trans, CommandType.Text);
            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@SCHEDULE_ID", taskScheduleId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@SCHEDULE_NAME", row.SCHEDULE_NAME.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@SCHEDULE_DESC", row.IsSCHEDULE_DESCNull() ? DBNull.Value : (object)row.SCHEDULE_DESC));
                cmd.Parameters.Add(CreateParameter("@TASK_ID", row.TASK_ID));
                cmd.Parameters.Add(CreateParameter("@ENABLED", row.ENABLED));
                cmd.Parameters.Add(CreateParameter("@START_DATE", row.START_DATE));
                cmd.Parameters.Add(CreateParameter("@END_DATE", row.IsEND_DATENull() ? DBNull.Value : (object)row.END_DATE));
                cmd.Parameters.Add(CreateParameter("@START_TIME", row.START_TIME));
                cmd.Parameters.Add(CreateParameter("@END_TIME", row.IsEND_TIMENull() ? DBNull.Value : (object)row.END_TIME));
                cmd.Parameters.Add(CreateParameter("@HOLIDAY_CAL_ID", row.IsHOLIDAY_CAL_IDNull() ? DBNull.Value : (object)row.HOLIDAY_CAL_ID));
                cmd.Parameters.Add(CreateParameter("@SCHEDULE_TYPE_ID", row.IsSCHEDULE_TYPE_IDNull() ? DBNull.Value : (object)row.SCHEDULE_TYPE_ID));
                cmd.Parameters.Add(CreateParameter("@T1", row.IsT1Null() ? DBNull.Value : (object)row.T1));
                cmd.Parameters.Add(CreateParameter("@DAILY_EVERY_DAYS", row.IsDAILY_EVERY_DAYSNull() ? DBNull.Value : (object)row.DAILY_EVERY_DAYS));
                cmd.Parameters.Add(CreateParameter("@WEEKLY_EVERY_WEEKS", row.IsWEEKLY_EVERY_WEEKSNull() ? DBNull.Value : (object)row.WEEKLY_EVERY_WEEKS));
                cmd.Parameters.Add(CreateParameter("@SUN", row.SUN));
                cmd.Parameters.Add(CreateParameter("@MON", row.MON));
                cmd.Parameters.Add(CreateParameter("@TUE", row.THU));
                cmd.Parameters.Add(CreateParameter("@WED", row.WED));
                cmd.Parameters.Add(CreateParameter("@THU", row.THU));
                cmd.Parameters.Add(CreateParameter("@FRI", row.FRI));
                cmd.Parameters.Add(CreateParameter("@SAT", row.SAT));
                cmd.Parameters.Add(CreateParameter("@MONTHLY_DAY_OF_MONTH", row.IsMONTHLY_DAY_OF_MONTHNull() ? DBNull.Value : (object)row.MONTHLY_DAY_OF_MONTH));
                cmd.Parameters.Add(CreateParameter("@MONTHLY_EVERY_DAY_OF_MONTH", row.IsMONTHLY_EVERY_DAY_OF_MONTHNull() ? DBNull.Value : (object)row.MONTHLY_EVERY_DAY_OF_MONTH));
                cmd.Parameters.Add(CreateParameter("@MONTHLY_EVERY_WEEK_OF_MONTH", row.IsMONTHLY_EVERY_WEEK_OF_MONTHNull() ? DBNull.Value : (object)row.MONTHLY_EVERY_WEEK_OF_MONTH));
                cmd.Parameters.Add(CreateParameter("@MONTHLY_EVERY_WEEKDAY_OF_MONTH", row.IsMONTHLY_EVERY_WEEKDAY_OF_MONTHNull() ? DBNull.Value : (object)row.MONTHLY_EVERY_WEEKDAY_OF_MONTH));
                cmd.Parameters.Add(CreateParameter("@MONTHLY_SELECTED_MONTHS", row.IsMONTHLY_SELECTED_MONTHSNull() ? DBNull.Value : (object)row.MONTHLY_SELECTED_MONTHS));
                cmd.Parameters.Add(CreateParameter("@REPEAT_ENABLED", row.REPEAT_ENABLED));
                cmd.Parameters.Add(CreateParameter("@REPEAT_INTERVAL", row.IsREPEAT_INTERVALNull() ? DBNull.Value : (object)row.REPEAT_INTERVAL));
                cmd.Parameters.Add(CreateParameter("@REPEAT_INTERVAL_TYPE", row.IsREPEAT_INTERVAL_TYPENull() ? DBNull.Value : (object)row.REPEAT_INTERVAL_TYPE));
                cmd.Parameters.Add(CreateParameter("@DEPENDENCIES", row.IsDEPENDENCIESNull() ? DBNull.Value : (object)row.DEPENDENCIES));
                cmd.Parameters.Add(CreateParameter("@FIRE_AFTER_DEPENDENCY", row.FIRE_AFTER_DEPENDENCY));
                cmd.Parameters.Add(CreateParameter("@REPEAT_ENABLED", row.REPEAT_ENABLED));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_BY", modifiedBy.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", DateTime.Now));

                cmd.ExecuteNonQuery();
            }

            return taskScheduleId;
        }

        protected override int UpdateTaskSchedule(int taskScheduleId, ChoTasksDataSet.CHO_TASK_SCHEDULESRow row, string modifiedBy, 
            string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.UPDATE_TASK_SCHEDULE, conn, trans, CommandType.Text);
            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@SCHEDULE_ID", taskScheduleId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@SCHEDULE_NAME", row.SCHEDULE_NAME.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@SCHEDULE_DESC", row.IsSCHEDULE_DESCNull() ? DBNull.Value : (object)row.SCHEDULE_DESC));
                cmd.Parameters.Add(CreateParameter("@TASK_ID", row.TASK_ID));
                cmd.Parameters.Add(CreateParameter("@ENABLED", row.ENABLED));
                cmd.Parameters.Add(CreateParameter("@START_DATE", row.START_DATE));
                cmd.Parameters.Add(CreateParameter("@END_DATE", row.IsEND_DATENull() ? DBNull.Value : (object)row.END_DATE));
                cmd.Parameters.Add(CreateParameter("@START_TIME", row.START_TIME));
                cmd.Parameters.Add(CreateParameter("@END_TIME", row.IsEND_TIMENull() ? DBNull.Value : (object)row.END_TIME));
                cmd.Parameters.Add(CreateParameter("@HOLIDAY_CAL_ID", row.IsHOLIDAY_CAL_IDNull() ? DBNull.Value : (object)row.HOLIDAY_CAL_ID));
                cmd.Parameters.Add(CreateParameter("@SCHEDULE_TYPE_ID", row.IsSCHEDULE_TYPE_IDNull() ? DBNull.Value : (object)row.SCHEDULE_TYPE_ID));
                cmd.Parameters.Add(CreateParameter("@T1", row.IsT1Null() ? DBNull.Value : (object)row.T1));
                cmd.Parameters.Add(CreateParameter("@DAILY_EVERY_DAYS", row.IsDAILY_EVERY_DAYSNull() ? DBNull.Value : (object)row.DAILY_EVERY_DAYS));
                cmd.Parameters.Add(CreateParameter("@WEEKLY_EVERY_WEEKS", row.IsWEEKLY_EVERY_WEEKSNull() ? DBNull.Value : (object)row.WEEKLY_EVERY_WEEKS));
                cmd.Parameters.Add(CreateParameter("@SUN", row.SUN));
                cmd.Parameters.Add(CreateParameter("@MON", row.MON));
                cmd.Parameters.Add(CreateParameter("@TUE", row.TUE));
                cmd.Parameters.Add(CreateParameter("@WED", row.WED));
                cmd.Parameters.Add(CreateParameter("@THU", row.THU));
                cmd.Parameters.Add(CreateParameter("@FRI", row.FRI));
                cmd.Parameters.Add(CreateParameter("@SAT", row.SAT));
                cmd.Parameters.Add(CreateParameter("@MONTHLY_DAY_OF_MONTH", row.IsMONTHLY_DAY_OF_MONTHNull() ? DBNull.Value : (object)row.MONTHLY_DAY_OF_MONTH));
                cmd.Parameters.Add(CreateParameter("@MONTHLY_EVERY_DAY_OF_MONTH", row.IsMONTHLY_EVERY_DAY_OF_MONTHNull() ? DBNull.Value : (object)row.MONTHLY_EVERY_DAY_OF_MONTH));
                cmd.Parameters.Add(CreateParameter("@MONTHLY_EVERY_WEEK_OF_MONTH", row.IsMONTHLY_EVERY_WEEK_OF_MONTHNull() ? DBNull.Value : (object)row.MONTHLY_EVERY_WEEK_OF_MONTH));
                cmd.Parameters.Add(CreateParameter("@MONTHLY_EVERY_WEEKDAY_OF_MONTH", row.IsMONTHLY_EVERY_WEEKDAY_OF_MONTHNull() ? DBNull.Value : (object)row.MONTHLY_EVERY_WEEKDAY_OF_MONTH));
                cmd.Parameters.Add(CreateParameter("@MONTHLY_SELECTED_MONTHS", row.IsMONTHLY_SELECTED_MONTHSNull() ? DBNull.Value : (object)row.MONTHLY_SELECTED_MONTHS));
                cmd.Parameters.Add(CreateParameter("@REPEAT_ENABLED", row.REPEAT_ENABLED));
                cmd.Parameters.Add(CreateParameter("@REPEAT_INTERVAL", row.IsREPEAT_INTERVALNull() ? DBNull.Value : (object)row.REPEAT_INTERVAL));
                cmd.Parameters.Add(CreateParameter("@REPEAT_INTERVAL_TYPE", row.IsREPEAT_INTERVAL_TYPENull() ? DBNull.Value : (object)row.REPEAT_INTERVAL_TYPE));
                cmd.Parameters.Add(CreateParameter("@DEPENDENCIES", row.IsDEPENDENCIESNull() ? DBNull.Value : (object)row.DEPENDENCIES));
                cmd.Parameters.Add(CreateParameter("@FIRE_AFTER_DEPENDENCY", row.FIRE_AFTER_DEPENDENCY));
                cmd.Parameters.Add(CreateParameter("@REPEAT_ENABLED", row.REPEAT_ENABLED));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_BY", modifiedBy.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", DateTime.Now));

                cmd.ExecuteNonQuery();
            }

            return taskScheduleId;
        }

        protected override void DeleteTaskSchedule(int taskScheduleId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            DeleteTaskBatch(taskScheduleId, modifiedBy, modifiedByHostName, conn, trans);
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.DELETE_TASK_SCHEDULE, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@SCHEDULE_ID", taskScheduleId));

                cmd.ExecuteNonQuery();
            }
        }

        protected override bool IsValidTaskStatus(int statusId, IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.IS_VALID_TASK_STATUS, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@TASK_STATUS_ID", statusId.ToDbValue<int>()));
                return cmd.ExecuteScalar().CastTo<int>() == 1;
            }
        }

        protected override bool IsTaskBatchExists(int batchId, int taskId, int scheduleId, IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.IS_TASK_BATCH_EXISTS, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@BATCH_ID", batchId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@TASK_ID", taskId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@SCHEDULE_ID", scheduleId.ToDbValue<int>()));
                return cmd.ExecuteScalar().CastTo<int>() == 1;
            }
        }

        protected override void AddTaskBatch(int batchId, int taskId, int scheduleId, int taskStatus,
            string cmdParameters, string machineName, string logMsg, DateTime processStart, DateTime? processEnd, TimeSpan? duration,
            string nextRunTime, int exitCode, int? retryCount, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = null;
            cmd = CreateCommand(ChoDataModelSql.INSERT_TASK_BATCH, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@BATCH_ID", batchId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@TASK_ID", taskId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@SCHEDULE_ID", scheduleId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@TASK_STATUS", taskStatus));
                cmd.Parameters.Add(CreateParameter("@CMD_PARAMETERS", cmdParameters.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MACHINE_NAME", machineName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@LOG_MSG", logMsg.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@START_TIME", processStart));
                cmd.Parameters.Add(CreateParameter("@END_TIME", processEnd.ToDbValue<DateTime?>()));
                cmd.Parameters.Add(CreateParameter("@DURATION", duration.ToDbValue<TimeSpan?>()));
                cmd.Parameters.Add(CreateParameter("@NEXT_RUN_TIME", nextRunTime.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@EXIT_CODE", exitCode));
                cmd.Parameters.Add(CreateParameter("@RETRY_COUNT", retryCount));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_BY", modifiedBy));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", DateTime.Now));

                cmd.ExecuteNonQuery();
            }
        }

        protected override void UpdateTaskBatch(int batchId, int taskId, int scheduleId, int taskStatus,
            string cmdParameters, string machineName, string logMsg, DateTime processStart, DateTime? processEnd, TimeSpan? duration,
            string nextRunTime, int exitCode, int? retryCount, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = null;
            cmd = CreateCommand(ChoDataModelSql.UPDATE_TASK_BATCH, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@BATCH_ID", batchId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@TASK_ID", taskId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@SCHEDULE_ID", scheduleId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@TASK_STATUS", taskStatus));
                cmd.Parameters.Add(CreateParameter("@CMD_PARAMETERS", cmdParameters.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MACHINE_NAME", machineName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@LOG_MSG", logMsg.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@START_TIME", processStart));
                cmd.Parameters.Add(CreateParameter("@END_TIME", processEnd.ToDbValue<DateTime?>()));
                cmd.Parameters.Add(CreateParameter("@DURATION", duration.ToDbValue<TimeSpan?>()));
                cmd.Parameters.Add(CreateParameter("@NEXT_RUN_TIME", nextRunTime.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@EXIT_CODE", exitCode));
                cmd.Parameters.Add(CreateParameter("@RETRY_COUNT", retryCount));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_BY", modifiedBy));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", DateTime.Now));

                cmd.ExecuteNonQuery();
            }
        }

        protected override void DeleteTaskBatch(int scheduleId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = null;
            cmd = CreateCommand(ChoDataModelSql.DELETE_TASK_BATCH, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@SCHEDULE_ID", scheduleId.ToDbValue<int>()));

                cmd.ExecuteNonQuery();
            }
        }

        protected override IDataReader GetAllHolidayCalendars(IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_HOLIDAY_CAL, conn, trans, CommandType.Text);
            return cmd.ExecuteReader();
        }

        protected override int GetHolidayCalendarId(string calName, IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_HOLIDAY_CAL_ID, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@CAL_NAME", calName.ToDbValue<string>()));
                return cmd.ExecuteScalar().CastTo<int>();
            }
        }

        protected override int AddHolidayCalendar(int calId, string calName, string calDesc, string modifiedBy, string modifiedByHostName, 
            IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.INSERT_HOLIDAY_CAL, conn, trans, CommandType.Text);
            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@CAL_ID", calId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@CAL_NAME", calName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@CAL_DESC", calDesc.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_BY", modifiedBy.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", DateTime.Now));

                cmd.ExecuteNonQuery();
            }
            return calId;
        }
        
        protected override string GetCalendarName(int calId, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_HOLIDAY_CAL_NAME, conn, trans, CommandType.Text);
            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@CAL_ID", calId.ToDbValue<int>()));

                return cmd.ExecuteScalar().CastTo<string>();
            }
        }

        protected override int UpdateHolidayCalendar(int calId, string calName, string calDesc, string modifiedBy, string modifiedByHostName, 
            IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.UPDATE_HOLIDAY_CAL, conn, trans, CommandType.Text);
            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@CAL_ID", calId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@CAL_NAME", calName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@CAL_DESC", calDesc.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_BY", modifiedBy.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", DateTime.Now));

                cmd.ExecuteNonQuery();
            }
            return calId;
        }

        protected override int DeleteHolidayCalendar(string calName, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.DELETE_HOLIDAY_CAL, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@CAL_NAME", calName.ToDbValue<string>()));
                return cmd.ExecuteNonQuery();
            }
        }

        protected override bool IsHolidayCalendarDateExists(int calId, DateTime calDate, IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.IS_HOLIDAY_CAL_DATE_EXISTS, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@CAL_ID", calId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@CAL_DATE", calDate.ToDbValue<DateTime>()));
                return cmd.ExecuteScalar().CastTo<bool>();
            }
        }

        protected override void AddHolidayCalendarDate(int calId, DateTime calDate, string calDateDesc, string modifiedBy, 
            string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = null;
            cmd = CreateCommand(ChoDataModelSql.INSERT_HOLIDAY_CAL_DATE, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@CAL_ID", calId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@CAL_DATE", calDate.ToDbValue<DateTime>()));
                cmd.Parameters.Add(CreateParameter("@CAL_DATE_DESC", calDateDesc.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_BY", modifiedBy.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", DateTime.Now));

                cmd.ExecuteNonQuery();
            }
        }

        protected override void UpdateHolidayCalendarDate(int calId, DateTime calDate, string calDateDesc, string modifiedBy, 
            string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = null;
            cmd = CreateCommand(ChoDataModelSql.UPDATE_HOLIDAY_CAL_DATE, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@CAL_ID", calId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@CAL_DATE", calDate.ToDbValue<DateTime>()));
                cmd.Parameters.Add(CreateParameter("@CAL_DATE_DESC", calDateDesc.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_BY", modifiedBy.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@MODIFIED_DATE", DateTime.Now));

                cmd.ExecuteNonQuery();
            }
        }

        protected override void DeleteHolidayCalendarDate(int calId, DateTime calDate, string modifiedBy, string modifiedByHostName, 
            IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.DELETE_HOLIDAY_CAL_DATE, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@CAL_ID", calId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@CAL_DATE", calDate.ToDbValue<DateTime>()));

                cmd.ExecuteNonQuery();
            }
        }

        protected override IDataReader GetAllHolidayCalendarDatesById(int holidayCalId, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_HOLIDAY_CAL_DATES_BY_ID, conn, trans, CommandType.Text);
            cmd.Parameters.Add(CreateParameter("@CAL_ID", holidayCalId.ToDbValue<int>()));

            return cmd.ExecuteReader();
        }

        protected override IDataReader GetAllHolidayCalendarDatesByName(string holidayCalName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_HOLIDAY_CAL_DATES_BY_NAME, conn, null, CommandType.Text);
            cmd.Parameters.Add(CreateParameter("@CAL_NAME", holidayCalName.ToDbValue<string>()));

            return cmd.ExecuteReader();
        }

        protected override IDataReader GetAllPolicy(IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_POLICY, conn, null, CommandType.Text);
            return cmd.ExecuteReader();
        }

        protected override IDataReader GetAllUsers(IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_USERS, conn, null, CommandType.Text);
            return cmd.ExecuteReader();
        }

        protected override int GetUserId(string name, IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_USER_ID, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@NAME", name.ToDbValue<string>()));
                return cmd.ExecuteScalar().CastTo<int>();
            }
        }

        protected override int AddUser(int id, string name, string fullName, string password, bool enabled, string modifiedBy, string modifiedByHostName, 
            IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.INSERT_USER, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@ID", id.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@NAME", name.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@FULL_NAME", fullName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@PASSWORD", password.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@ENABLED", enabled));

                cmd.ExecuteNonQuery();
            }
            return id;
        }

        protected override int UpdateUser(int id, string name, string fullName, string password, bool enabled, string modifiedBy, string modifiedByHostName, 
            IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.UPDATE_USER, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@ID", id.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@NAME", name.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@FULL_NAME", fullName.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@PASSWORD", password.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@ENABLED", enabled));

                cmd.ExecuteNonQuery();
            }
            return id;
        }

        protected override void DeleteUser(int id, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            DeleteUserRole(id, modifiedBy, modifiedByHostName, conn, trans);

            IDbCommand cmd = CreateCommand(ChoDataModelSql.DELETE_USER, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@ID", id.ToDbValue<int>()));
                cmd.ExecuteNonQuery();
            }
        }

        protected override IDataReader GetAllRoles(IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_ROLES, conn, null, CommandType.Text);
            return cmd.ExecuteReader();
        }

        protected override IDataReader GetAllUserRoles(int roleId, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_USER_ROLE, conn, null, CommandType.Text);
            cmd.Parameters.Add(CreateParameter("@ROLE_ID", roleId.ToDbValue<int>()));
            return cmd.ExecuteReader();
        }

        protected override IDataReader GetAllRolePolicy(int roleId, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_ROLE_POLICY, conn, null, CommandType.Text);
            cmd.Parameters.Add(CreateParameter("@ROLE_ID", roleId.ToDbValue<int>()));
            return cmd.ExecuteReader();
        }

        protected override int GetRoleId(string name, IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ROLE_ID, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@NAME", name.ToDbValue<string>()));
                return cmd.ExecuteScalar().CastTo<int>();
            }
        }

        protected override int AddRole(int id, string name, string comment, bool enabled, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.INSERT_ROLE, conn, trans, CommandType.Text);
            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@ID", id.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@NAME", name.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@COMMENT", comment.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@ENABLED", enabled));

                cmd.ExecuteNonQuery();
            }
            return id;
        }

        protected override int UpdateRole(int id, string name, string comment, bool enabled, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.UPDATE_ROLE, conn, trans, CommandType.Text);
            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@ID", id.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@NAME", name.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@COMMENT", comment.ToDbValue<string>()));
                cmd.Parameters.Add(CreateParameter("@ENABLED", enabled));

                cmd.ExecuteNonQuery();
            }
            return id;
        }

        protected override void DeleteRole(int roleId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            DeleteUserRoleForRole(roleId, modifiedBy, modifiedByHostName, conn, trans);
            DeleteRolePolicyForRole(roleId, modifiedBy, modifiedByHostName, conn, trans);

            IDbCommand cmd = null;
            cmd = CreateCommand(ChoDataModelSql.DELETE_ROLE, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@ID", roleId.ToDbValue<int>()));
                cmd.ExecuteNonQuery();
            }
        }
        protected override void DeleteUserRoleForRole(int roleId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = null;
            cmd = CreateCommand(ChoDataModelSql.DELETE_USER_ROLES_FOR_ROLE, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@ID", roleId.ToDbValue<int>()));
                cmd.ExecuteNonQuery();
            }
        }
        protected override void DeleteRolePolicyForRole(int roleId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = null;
            cmd = CreateCommand(ChoDataModelSql.DELETE_ROLE_POLICY_FOR_ROLE, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@ID", roleId.ToDbValue<int>()));
                cmd.ExecuteNonQuery();
            }
        }

        protected override bool IsRolePolicyExists(int roleId, int policyId, IDbConnection conn, IDbTransaction trans)
        {
            using (IDbCommand cmd = CreateCommand(ChoDataModelSql.IS_ROLE_POLICY_EXISTS, conn, trans, CommandType.Text))
            {
                cmd.Parameters.Add(CreateParameter("@ROLE_ID", roleId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@POLICY_ID", policyId.ToDbValue<int>()));
                return cmd.ExecuteScalar().CastTo<bool>();
            }
        }

        protected override void AddRolePolicy(int roleId, int policyId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.INSERT_ROLE_POLICY, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@ROLE_ID", roleId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@POLICY_ID", policyId.ToDbValue<int>()));

                cmd.ExecuteNonQuery();
            }
        }

        protected override void DeleteRolePolicy(int roleId, int policyId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.DELETE_ROLE_POLICY, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@ROLE_ID", roleId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@POLICY_ID", policyId.ToDbValue<int>()));

                cmd.ExecuteNonQuery();
            }
        }

        protected override void AddUserRole(int userId, int roleId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.INSERT_USER_ROLE, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@USER_ID", userId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@ROLE_ID", roleId.ToDbValue<int>()));
                cmd.ExecuteNonQuery();
            }
        }

        protected override void DeleteUserRole(int userId, int roleId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.DELETE_USER_ROLE_FOR_USER_ROLE, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@USER_ID", userId.ToDbValue<int>()));
                cmd.Parameters.Add(CreateParameter("@ROLE_ID", roleId.ToDbValue<int>()));
                cmd.ExecuteNonQuery();
            }
        }

        protected override void DeleteUserRole(int userId, string modifiedBy, string modifiedByHostName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.DELETE_USER_ROLE, conn, trans, CommandType.Text);

            using (cmd)
            {
                cmd.Parameters.Add(CreateParameter("@USER_ID", userId.ToDbValue<int>()));
                cmd.ExecuteNonQuery();
            }
        }

        protected override IDataReader GetPolicyForUser(string userName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_POLICY_FOR_USER, conn, null, CommandType.Text);
            cmd.Parameters.Add(CreateParameter("@USER_NAME", userName.ToDbValue<string>()));

            return cmd.ExecuteReader();
        }

        protected override IDataReader GetAllDepedencyTasks(string jobName, IDbConnection conn, IDbTransaction trans)
        {
            IDbCommand cmd = CreateCommand(ChoDataModelSql.GET_ALL_DEPENDENCY_TASKS, conn, null, CommandType.Text);
            cmd.Parameters.Add(CreateParameter("@JOB_NAME", "%({0})%".FormatString(jobName.ToDbValue<string>())));

            return cmd.ExecuteReader();
        }

        public override IDbConnection Connection
        {
            get { return new SQLiteConnection(_modelSettings.ConnectionString); }
        }

        protected override int GetMaxTaskId()
        {
            return ExecuteScalarSql<int>(ChoDataModelSql.GET_MAX_TASK_ID);
        }

        protected override int GetMaxTaskGroupId()
        {
            return ExecuteScalarSql<int>(ChoDataModelSql.GET_MAX_TASK_GRP_ID);
        }

        protected override int GetMaxTaskScheduleId()
        {
            return ExecuteScalarSql<int>(ChoDataModelSql.GET_MAX_TASK_SCHEDULE_ID);
        }

        protected override int GetMaxHolidayCalendarId()
        {
            return ExecuteScalarSql<int>(ChoDataModelSql.GET_MAX_HOLIDAY_CAL_ID);
        }

        protected override int GetMaxUserId()
        {
            return ExecuteScalarSql<int>(ChoDataModelSql.GET_MAX_USER_ID);
        }

        protected override int GetMaxRoleId()
        {
            return ExecuteScalarSql<int>(ChoDataModelSql.GET_MAX_ROLE_ID);
        }

        private T ExecuteScalarSql<T>(string cmdText)
        {
            using (IDbConnection conn = Connection)
            {
                conn.Open();

                try
                {
                    IDbCommand cmd = null;

                    cmd = CreateCommand(cmdText, conn, null, CommandType.Text);

                    return cmd.ExecuteScalar().CastTo<T>();
                }
                catch (Exception ex)
                {
                    ChoTrace.Write(ex);
                    throw;
                }
            }
        }

        protected override IDbCommand CreateCommand(string cmdText, IDbConnection conn, IDbTransaction trans = null, CommandType cmdType = CommandType.Text)
        {
            SQLiteCommand cmd = new SQLiteCommand(cmdText, conn as SQLiteConnection);
            if (trans != null)
                cmd.Transaction = trans as SQLiteTransaction;

            cmd.CommandType = cmdType;

            return cmd;
        }

        protected override DbParameter CreateParameter(string paramName, object paramValue = null)
        {
            return new SQLiteParameter(paramName, paramValue);
        }

        public override void RefreshDB()
        {
            string sql = ChoAssembly.ReadResourceTextFile("{0}.{1}".FormatString(GetType().Namespace, "RefreshDBScript.sql"));
            if (sql.IsNullOrWhiteSpace())
                return;

            using (IDbConnection conn = Connection)
            {
                conn.Open();

                IDbTransaction trans = conn.BeginTransaction();

                try
                {
                    IDbCommand cmd = CreateCommand(sql, conn, trans, CommandType.Text);
                    cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    ChoTrace.Write(ex);
                    throw;
                }
            }
        }
    }
}
